import pymysql

# ========== 数据库连接配置 ==========
DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "password": "123456",
    "database": "cdm",
    "charset": "utf8mb4"
}

OUTPUT_FILE = "cdm_select.sql"

def get_connection():
    return pymysql.connect(**DB_CONFIG)

def get_all_tables(cursor):
    cursor.execute("SHOW TABLES;")
    return [row[0] for row in cursor.fetchall()]

def get_columns(cursor, table_name):
    cursor.execute(f"SHOW COLUMNS FROM `{table_name}`;")
    return [row[0] for row in cursor.fetchall()]

def map_yy_field(col):
    """
    针对不同的 yy_ 字段生成不同的替换规则
    # 8个yy开头的字段
    """
    if col == "yy_id":
        return "NULL AS yy_id"
    elif col == "yy_upload_status":
        return "0 AS yy_upload_status"
    elif col == "yy_etl_time":
        return "NOW() AS yy_etl_time"
    elif col == "yy_upload_time":
        return "NULL AS yy_upload_time"
    elif col == "yy_batch_time":
        return "NULL AS yy_batch_time"
    elif col == "yy_record_batch_id":
        return "0 AS yy_record_batch_id"
    elif col == "yy_backfill_time":
        return "NULL AS yy_backfill_time"
    elif col == "yy_backfill_status":
        return "0 AS yy_backfill_status"
    else:
        # 其他以 yy_ 开头但未明确定义的字段，可统一设定默认值
        return f"NULL AS {col}"

def generate_select_sql(table_name, columns):
    """生成 SELECT 语句：针对 yy_ 字段做特殊处理"""
    select_parts = []
    for col in columns:
        if col.startswith("yy_"):
            select_parts.append(map_yy_field(col))
        else:
            select_parts.append(f"{col} AS {col}")
    column_list = ",\n    ".join(select_parts)
    sql = f"SELECT\n    {column_list}\nFROM {table_name};"
    return sql

def main():
    conn = get_connection()
    cursor = conn.cursor()

    all_tables = get_all_tables(cursor)

    with open(OUTPUT_FILE, "w", encoding="utf-8") as f:
        for table in all_tables:
            columns = get_columns(cursor, table)
            select_sql = generate_select_sql(table, columns)
            f.write(f"-- {table} 表的 select 语句\n")
            f.write(select_sql + "\n\n")

    cursor.close()
    conn.close()
    print(f"✅ 已生成文件: {OUTPUT_FILE}")

if __name__ == "__main__":
    main()
